﻿using System;
using System.Data;
using System.Data.SQLite;

namespace CSharpHelpers.DBAccess
{
    class SQLiteHelper
    {
        private static string defaultDBPath = String.Empty;
        private static string defaultConn = String.Empty;


        public static void SetDataBase(string defaultPath, string dbFilename)
        {
            defaultDBPath = defaultPath[defaultPath.Length - 1].ToString() == "\\" ? defaultPath : defaultPath + "\\";
            defaultConn = String.Concat("Data Source=", defaultDBPath, dbFilename);
        }

        private static string BuildConnStr(string dbFileName)
        {
            string connStr = defaultConn;
            if (!String.IsNullOrEmpty(dbFileName))
            {
                connStr = String.Concat("Data Source=", defaultDBPath, dbFileName);
            }
            return connStr;
        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
                //foreach (SQLiteParameter parm in cmdParms)
                //  cmd.Parameters.Add(parm);
            }
        }

        public static int ExecuteNonQuery(string cmdText, SQLiteParameter[] cmdParms)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(defaultConn))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
                return val;
            }
        }

        public static int ExecuteNonQuery(string cmdText, SQLiteParameter[] cmdParms, string databaseName)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
                return val;
            }
        }

        public static object ExecuteScalar(string cmdText, SQLiteParameter[] cmdParms)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(defaultConn))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return obj;
            }
        }

        public static object ExecuteScalar(string cmdText, SQLiteParameter[] cmdParms, string databaseName)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return obj;
            }
        }

        public static DataSet GetDataSet(string cmdText, SQLiteParameter[] cmdParms)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(defaultConn))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                SQLiteDataAdapter adpt = new SQLiteDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
        }

        public static DataSet GetDataSet(string cmdText, SQLiteParameter[] cmdParms, string databaseName)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                SQLiteDataAdapter adpt = new SQLiteDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds;
            }
        }

        public static DataTable GetDataTable(string cmdText, SQLiteParameter[] cmdParms)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(defaultConn))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                SQLiteDataAdapter adpt = new SQLiteDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds.Tables[0];
            }
        }

        public static DataTable GetDataTable(string cmdText, SQLiteParameter[] cmdParms, string databaseName)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            using (SQLiteConnection conn = new SQLiteConnection(BuildConnStr(databaseName)))
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                SQLiteDataAdapter adpt = new SQLiteDataAdapter();
                adpt.SelectCommand = cmd;
                DataSet ds = new DataSet();
                adpt.Fill(ds);
                cmd.Parameters.Clear();
                conn.Close();
                return ds.Tables[0];
            }
        }

        public static SQLiteDataReader GetDataReader(string cmdText, SQLiteParameter[] cmdParms)
        {
            SQLiteConnection conn = new SQLiteConnection(defaultConn);
            SQLiteCommand cmd = new SQLiteCommand();
            try
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        public static SQLiteDataReader GetDataReader(string cmdText, SQLiteParameter[] cmdParms, string databaseName)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            SQLiteConnection conn = new SQLiteConnection(BuildConnStr(databaseName));
            try
            {
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return reader;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
    }
}
